/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package FlightSelect;

import common.*;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.StringTokenizer;
import java.util.Vector;

/**
 *  need to modify the obj content and query
 * @author cipher
 */
public class FlightSelectPageBean extends PageBusiness {

    private Connection con;
    private FlightSelectBean fsb;
    private int maxID;
    private int price;
    private String cityString;

    public FlightSelectPageBean() throws Exception{
        super();
        con = DbConnector.getConnection();
        v = new Vector();
        maxID = 0;
        cityString = "";
    }
    
    public Vector getResult()throws Exception
    {
        return v;
    }
    
    public void setFsb(FlightSelectBean fsb) {
        this.fsb = fsb;
    }
    
    public FlightSelectBean getFsb () {
        return fsb;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    public String getCityString() {
        return cityString;
    }

    public void setCityString(String cityString) {
        this.cityString = cityString;
    }
    
    @Override
    public int getAvailableCount() throws Exception {
        // TEMP: FOR TEST, SHOULD BE "FLIGHT"
        return this.getAvailableCountHelper(con,"AIRLINE");
    }

    @Override
    public PageBean listData(String page)throws Exception {
        PageBean pageBean = null;
        return pageBean;
    }
    
    public PageBean Proceed(int TicketNum, int control, String UID, String Exdate, int pricetotal, int overideMaxID, int overide) throws Exception {
        // put data query and procedure for retrival any of information
        try {
            int price1 = 0;
            int price2 = 0;
            int price3 = 0;
            int price4 = 0;
            int price5 = 0;
            String ID1 = null;
            String ID2 = null;
            String ID3 = null;
            String ID4 = null;
            String ID5 = null;
            String re = null;
            int price;
            int coach;
            int first;

            String sqlMax = "select max(RESERVATION_ID) as LARGEST from RESERVATION";
            
            //this is only for single trip query, all things from this point on should be done twice for round trip--NO NEED !!
            String sqlFirstFlight1 = "select FLIGHT_PRICE, FLIGHT_ID, FLIGHT_NUMBER, FLIGHT_COACH_SEAT_REMAINING, FLIGHT_FIRST_SEAT_REMAINING," +
                    "to_char(FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_DEPARTURE_TIME, to_char(FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_ARRIVAL_TIME, " +
                    "AIRCRAFT_ID, ap1.AIRPORT_CITY as DEPART_CITY, ap2.AIRPORT_CITY as ARRIVAL_CITY, al.AIRLINE_NAME as ANAME " +
                    "from FLIGHT fl, AIRPORT ap1, AIRPORT ap2, AIRLINE al where fl.DEPART_FROM = ap1.AIRPORT_ID and fl.ARRIVE_AT = ap2.AIRPORT_ID and fl.AIRLINE_ID = al.AIRLINE_ID and fl.FLIGHT_ID = " + fsb.getFlightID1();
            String sqlFirstFlight2 = "select DEPART_FROM, FLIGHT_PRICE, FLIGHT_ID, FLIGHT_NUMBER, FLIGHT_COACH_SEAT_REMAINING, FLIGHT_FIRST_SEAT_REMAINING," +
                    "to_char(FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_DEPARTURE_TIME, to_char(FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_ARRIVAL_TIME, " +
                    "AIRCRAFT_ID, ap1.AIRPORT_CITY as DEPART_CITY, ap2.AIRPORT_CITY as ARRIVAL_CITY, al.AIRLINE_NAME as ANAME " +
                    "from FLIGHT fl, AIRPORT ap1, AIRPORT ap2, AIRLINE al where fl.DEPART_FROM = ap1.AIRPORT_ID and fl.ARRIVE_AT = ap2.AIRPORT_ID and fl.AIRLINE_ID = al.AIRLINE_ID and fl.FLIGHT_ID = " + fsb.getFlightID2();
            String sqlFirstFlight3 = "select DEPART_FROM, FLIGHT_PRICE, FLIGHT_ID, FLIGHT_NUMBER, FLIGHT_COACH_SEAT_REMAINING, FLIGHT_FIRST_SEAT_REMAINING," +
                    "to_char(FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_DEPARTURE_TIME, to_char(FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_ARRIVAL_TIME, " +
                    "AIRCRAFT_ID, ap1.AIRPORT_CITY as DEPART_CITY, ap2.AIRPORT_CITY as ARRIVAL_CITY, al.AIRLINE_NAME as ANAME " +
                    "from FLIGHT fl, AIRPORT ap1, AIRPORT ap2, AIRLINE al where fl.DEPART_FROM = ap1.AIRPORT_ID and fl.ARRIVE_AT = ap2.AIRPORT_ID and fl.AIRLINE_ID = al.AIRLINE_ID and fl.FLIGHT_ID = " + fsb.getFlightID3();
            String sqlFirstFlight4 = "select DEPART_FROM, FLIGHT_PRICE, FLIGHT_ID, FLIGHT_NUMBER, FLIGHT_COACH_SEAT_REMAINING, FLIGHT_FIRST_SEAT_REMAINING," +
                    "to_char(FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_DEPARTURE_TIME, to_char(FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_ARRIVAL_TIME, " +
                    "AIRCRAFT_ID, ap1.AIRPORT_CITY as DEPART_CITY, ap2.AIRPORT_CITY as ARRIVAL_CITY, al.AIRLINE_NAME as ANAME " +
                    "from FLIGHT fl, AIRPORT ap1, AIRPORT ap2, AIRLINE al where fl.DEPART_FROM = ap1.AIRPORT_ID and fl.ARRIVE_AT = ap2.AIRPORT_ID and fl.AIRLINE_ID = al.AIRLINE_ID and fl.FLIGHT_ID = " + fsb.getFlightID4();
            String sqlFirstFlight5 = "select DEPART_FROM, FLIGHT_PRICE, FLIGHT_ID, FLIGHT_NUMBER, FLIGHT_COACH_SEAT_REMAINING, FLIGHT_FIRST_SEAT_REMAINING," +
                    "to_char(FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_DEPARTURE_TIME, to_char(FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI') as FLIGHT_ARRIVAL_TIME, " +
                    "AIRCRAFT_ID, ap1.AIRPORT_CITY as DEPART_CITY, ap2.AIRPORT_CITY as ARRIVAL_CITY, al.AIRLINE_NAME as ANAME " +
                    "from FLIGHT fl, AIRPORT ap1, AIRPORT ap2, AIRLINE al where fl.DEPART_FROM = ap1.AIRPORT_ID and fl.ARRIVE_AT = ap2.AIRPORT_ID and fl.AIRLINE_ID = al.AIRLINE_ID and fl.FLIGHT_ID = " + fsb.getFlightID5();


 //           String sqlFirstFlightCoach1 = sqlFirstFlight1 + " and fl.FLIGHT_COACH_SEAT_REMAINING >= 1";

            //delete tuple in the FLIGHT table, should be according to the class for updating not deletion
            String sqlDelFirstFlight1 = "delete from FLIGHT where FLIGHT_ID = " + fsb.getFlightID1();
            String sqlDelFirstFlight2 = "delete from FLIGHT where FLIGHT_ID = " + fsb.getFlightID2();
            String sqlDelFirstFlight3 = "delete from FLIGHT where FLIGHT_ID = " + fsb.getFlightID3();
            String sqlDelFirstFlight4 = "delete from FLIGHT where FLIGHT_ID = " + fsb.getFlightID4();
            String sqlDelFirstFlight5 = "delete from FLIGHT where FLIGHT_ID = " + fsb.getFlightID5();

            //update COACH SEAT
            String sqlCoachUpdateFirstFlight1 = "update FLIGHT set FLIGHT_COACH_SEAT_REMAINING=FLIGHT_COACH_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID1();
            String sqlCoachUpdateFirstFlight2 = "update FLIGHT set FLIGHT_COACH_SEAT_REMAINING=FLIGHT_COACH_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID2();
            String sqlCoachUpdateFirstFlight3 = "update FLIGHT set FLIGHT_COACH_SEAT_REMAINING=FLIGHT_COACH_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID3();
            String sqlCoachUpdateFirstFlight4 = "update FLIGHT set FLIGHT_COACH_SEAT_REMAINING=FLIGHT_COACH_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID4();
            String sqlCoachUpdateFirstFlight5 = "update FLIGHT set FLIGHT_COACH_SEAT_REMAINING=FLIGHT_COACH_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID5();

            //update FIRST SEAT
            String sqlFirstUpdateFirstFlight1 = "update FLIGHT set FLIGHT_FIRST_SEAT_REMAINING=FLIGHT_FIRST_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID1();
            String sqlFirstUpdateFirstFlight2 = "update FLIGHT set FLIGHT_FIRST_SEAT_REMAINING=FLIGHT_FIRST_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID2();
            String sqlFirstUpdateFirstFlight3 = "update FLIGHT set FLIGHT_FIRST_SEAT_REMAINING=FLIGHT_FIRST_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID3();
            String sqlFirstUpdateFirstFlight4 = "update FLIGHT set FLIGHT_FIRST_SEAT_REMAINING=FLIGHT_FIRST_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID4();
            String sqlFirstUpdateFirstFlight5 = "update FLIGHT set FLIGHT_FIRST_SEAT_REMAINING=FLIGHT_FIRST_SEAT_REMAINING-" + TicketNum + " where FLIGHT_ID=" + fsb.getFlightID5();
          
            PageBean pageBean = new PageBean(this);
            Statement stmt = con.createStatement();
            ResultSet rset = stmt.executeQuery(sqlMax);
            if ( rset == null)
                System.out.println("wrong");
            else {
                System.out.println("RIGHT");
                rset.next();
            
                maxID = rset.getInt(1);
                maxID += 1;
            }
                       
//          int pageNum = Integer.parseInt(page);
               
            if (fsb.getHoop() >= 1) {
            rset = stmt.executeQuery(sqlFirstFlight1);
            Object[] obj = new Object[10];
            rset.next();
            obj[0] = new String(rset.getString("DEPART_CITY"));
            obj[1] = new String(rset.getString("ARRIVAL_CITY"));
            obj[2] = new String(rset.getString("ANAME"));
            obj[3] = new String(rset.getString("FLIGHT_NUMBER"));
            obj[4] = new String(rset.getString("FLIGHT_DEPARTURE_TIME"));
            obj[5] = new String(rset.getString("FLIGHT_ARRIVAL_TIME"));
            obj[6] = new String(rset.getString("FLIGHT_PRICE"));
            obj[7] = new String(rset.getString("FLIGHT_ID"));
            obj[8] = new String(String.valueOf(maxID));
            ID1 = rset.getString("FLIGHT_ID");
            coach = rset.getInt("FLIGHT_COACH_SEAT_REMAINING");
            first = rset.getInt("FLIGHT_FIRST_SEAT_REMAINING");

            cityString += (String) obj[0] + ";" + (String) obj[1] + ";";

            price1 = rset.getInt("FLIGHT_PRICE");

            if (control == 1) {
                if (fsb.getseatClass() == 1 ) {
                    //execute the update for coach seat if num>1, else delete
                    if (coach == TicketNum && first == 0)
                        stmt.executeQuery(sqlDelFirstFlight1);
                    else
                        stmt.executeQuery(sqlCoachUpdateFirstFlight1);
                }else {
                    if (first == TicketNum && coach == 0)
                        stmt.executeQuery(sqlDelFirstFlight1);
                    else
                        stmt.executeQuery(sqlFirstUpdateFirstFlight1);
                }
            }

            re = ID1;
            obj[9] = new String(re);

            

            //update
            // rset = stmt.executeQuery(sqlUpFirstFlight1c);
            //...
            v.add(obj);
            
                if (fsb.getHoop() >= 2) {
                    rset = stmt.executeQuery(sqlFirstFlight2);
//                    Object[] obj = new Object[7];
                    obj = new Object[10];
                    rset.next();
                    obj[0] = new String(rset.getString("DEPART_CITY"));
                    obj[1] = new String(rset.getString("ARRIVAL_CITY"));
                    obj[2] = new String(rset.getString("ANAME"));
                    obj[3] = new String(rset.getString("FLIGHT_NUMBER"));
                    obj[4] = new String(rset.getString("FLIGHT_DEPARTURE_TIME"));
                    obj[5] = new String(rset.getString("FLIGHT_ARRIVAL_TIME"));
                    obj[6] = new String(rset.getString("FLIGHT_PRICE"));
                    obj[7] = new String(rset.getString("FLIGHT_ID"));
                    obj[8] = new String(String.valueOf(maxID));
                    ID2 = rset.getString("FLIGHT_ID");
                    coach = rset.getInt("FLIGHT_COACH_SEAT_REMAINING");
                    first = rset.getInt("FLIGHT_FIRST_SEAT_REMAINING");

                    cityString += (String) obj[1] + ";";
                    price2 = rset.getInt("FLIGHT_PRICE");

                    if (control == 1) {
                        if (fsb.getseatClass() == 1 ) {
                            //execute the update for coach seat if num>1, else delete
                            if (coach == TicketNum && first == 0)
                                stmt.executeQuery(sqlDelFirstFlight2);
                            else
                                stmt.executeQuery(sqlCoachUpdateFirstFlight2);
                        }else {
                            if (first == TicketNum && coach == 0)
                                stmt.executeQuery(sqlDelFirstFlight2);
                            else
                                stmt.executeQuery(sqlFirstUpdateFirstFlight2);
                        }
                    }

                    re += ".";
                    re += ID2;
                    obj[9] = new String(re);

                    
                //...
                    //update
                    // rset = stmt.executeQuery(sqlUpFirstFlight2c);
                    v.add(obj);
                    
                
                    if (fsb.getHoop() >= 3) {
                        rset = stmt.executeQuery(sqlFirstFlight3);
 //                       Object[] obj = new Object[7];
//                        obj[0] = new String(rset.getString(page));
                        obj = new Object[10];
                        rset.next();
                        obj[0] = new String(rset.getString("DEPART_CITY"));
                        obj[1] = new String(rset.getString("ARRIVAL_CITY"));
                        obj[2] = new String(rset.getString("ANAME"));
                        obj[3] = new String(rset.getString("FLIGHT_NUMBER"));
                        obj[4] = new String(rset.getString("FLIGHT_DEPARTURE_TIME"));
                        obj[5] = new String(rset.getString("FLIGHT_ARRIVAL_TIME"));
                        obj[6] = new String(rset.getString("FLIGHT_PRICE"));
                        obj[7] = new String(rset.getString("FLIGHT_ID"));
                        obj[8] = new String(String.valueOf(maxID));
                        ID3 = rset.getString("FLIGHT_ID");
                        coach = rset.getInt("FLIGHT_COACH_SEAT_REMAINING");
                        first = rset.getInt("FLIGHT_FIRST_SEAT_REMAINING");

                        cityString += (String) obj[1] + ";";
                        price3 = rset.getInt("FLIGHT_PRICE");

                        if (control == 1) {
                            if (fsb.getseatClass() == 1 ) {
                                //execute the update for coach seat if num>1, else delete
                                if (coach == TicketNum && first == 0)
                                    stmt.executeQuery(sqlDelFirstFlight3);
                                else
                                    stmt.executeQuery(sqlCoachUpdateFirstFlight3);
                            }else {
                                if (first == TicketNum && coach == 0)
                                    stmt.executeQuery(sqlDelFirstFlight3);
                                else
                                    stmt.executeQuery(sqlFirstUpdateFirstFlight3);
                            }
                        }

                        re += ".";
                        re += ID3;
                        obj[9] = new String(re);

                        
                    //...
                        //update
                        // rset = stmt.executeQuery(sqlUpFirstFlight2c);
                        v.add(obj);
                        
                        
                        if (fsb.getHoop() >= 4) {
                            rset = stmt.executeQuery(sqlFirstFlight4);
 //                           obj[0] = new String(rset.getString(page));
                            obj = new Object[10];
                            rset.next();
                            obj[0] = new String(rset.getString("DEPART_CITY"));
                            obj[1] = new String(rset.getString("ARRIVAL_CITY"));
                            obj[2] = new String(rset.getString("ANAME"));
                            obj[3] = new String(rset.getString("FLIGHT_NUMBER"));
                            obj[4] = new String(rset.getString("FLIGHT_DEPARTURE_TIME"));
                            obj[5] = new String(rset.getString("FLIGHT_ARRIVAL_TIME"));
                            obj[6] = new String(rset.getString("FLIGHT_PRICE"));
                            obj[7] = new String(rset.getString("FLIGHT_ID"));
                            obj[8] = new String(String.valueOf(maxID));
                            ID4 = rset.getString("FLIGHT_ID");
                            coach = rset.getInt("FLIGHT_COACH_SEAT_REMAINING");
                            first = rset.getInt("FLIGHT_FIRST_SEAT_REMAINING");

                            cityString += (String) obj[1] + ";";
                            price4 = rset.getInt("FLIGHT_PRICE");

                            if (control == 1) {
                                if (fsb.getseatClass() == 1 ) {
                                    //execute the update for coach seat if num>1, else delete
                                    if (coach == TicketNum && first == 0)
                                        stmt.executeQuery(sqlDelFirstFlight4);
                                    else
                                        stmt.executeQuery(sqlCoachUpdateFirstFlight4);
                                }else {
                                    if (first == TicketNum && coach == 0)
                                        stmt.executeQuery(sqlDelFirstFlight4);
                                    else
                                        stmt.executeQuery(sqlFirstUpdateFirstFlight4);
                                }
                            }

                            re += ".";
                            re += ID4;
                            obj[9] = new String(re);

                            

                            //update
                            // rset = stmt.executeQuery(sqlUpFirstFlight2c);
                            v.add(obj);

                            if (fsb.getHoop() >= 5) {
                                rset = stmt.executeQuery(sqlFirstFlight5);
//                                obj[0] = new String(rset.getString(page));
                                obj = new Object[10];
                                rset.next();
                                obj[0] = new String(rset.getString("DEPART_CITY"));
                                obj[1] = new String(rset.getString("ARRIVAL_CITY"));
                                obj[2] = new String(rset.getString("ANAME"));
                                obj[3] = new String(rset.getString("FLIGHT_NUMBER"));
                                obj[4] = new String(rset.getString("FLIGHT_DEPARTURE_TIME"));
                                obj[5] = new String(rset.getString("FLIGHT_ARRIVAL_TIME"));
                                obj[6] = new String(rset.getString("FLIGHT_PRICE"));
                                obj[7] = new String(rset.getString("FLIGHT_ID"));
                                obj[8] = new String(String.valueOf(maxID));
                                ID5 = rset.getString("FLIGHT_ID");
                                coach = rset.getInt("FLIGHT_COACH_SEAT_REMAINING");
                                first = rset.getInt("FLIGHT_FIRST_SEAT_REMAINING");

                                cityString += (String) obj[1] + ";";
                                price5 = rset.getInt("FLIGHT_PRICE");

                                if (control == 1) {
                                    if (fsb.getseatClass() == 1 ) {
                                        //execute the update for coach seat if num>1, else delete
                                        if (coach == TicketNum && first == 0)
                                            stmt.executeQuery(sqlDelFirstFlight5);
                                        else
                                            stmt.executeQuery(sqlCoachUpdateFirstFlight5);
                                    }else {
                                        if (first == TicketNum && coach == 0)
                                            stmt.executeQuery(sqlDelFirstFlight5);
                                        else
                                            stmt.executeQuery(sqlFirstUpdateFirstFlight5);
                                    }
                                }

                                re += ".";
                                re += ID5;
                                obj[9] = new String(re);

                                
                                //update
                                // rset = stmt.executeQuery(sqlUpFirstFlight2c);
                                v.add(obj);
                                
                            }
                        }
                    }
                }
            price = price1 + price2 + price3 + price4 + price5;

            this.price = price;

            //insert to the reservation table
            //id, status, class, seat number, fare, rv_date, fl_id, usr_id(virtual user)
            //need to find the number of flights


            if (control == 1) {
                if (price1 != 0) {


                    String InsertReservationCoach = "insert into RESERVATION values (" + overideMaxID + ",'Proceed', 'Coach', '134'," + pricetotal + ",NULL)";
                    String InsertReservationFirst = "insert into RESERVATION values (" + overideMaxID + ",'Proceed', 'First', '134'," + pricetotal + ",NULL)";
                    String InsertReToFl = "insert into MAP_RESERVATION_FLIGHT values (" + overideMaxID + "," + ID1 + ")";
                    String InsertReToUr = "insert into MAP_RESERVATION_USER values (" + overideMaxID + "," + "'"+ UID + "')";//this should be from session attribute
                    if (fsb.getseatClass() == 1 ) {
                        if ( overide != 0 )//null for back trip identifier
                            rset = stmt.executeQuery(InsertReservationCoach);
                    }else {
                        if ( overide != 0 )
                            rset = stmt.executeQuery(InsertReservationFirst);
                    }
                    rset = stmt.executeQuery(InsertReToFl);
                    if ( overide != 0 )
                        rset = stmt.executeQuery(InsertReToUr);

                    if (price2 != 0) {
                        //InsertReservation = "insert into RESERVATION values (" + maxID + ",'SHIT', 'SHIT', '134'," + price2 + "," + "NULL)";
                        InsertReToFl = "insert into MAP_RESERVATION_FLIGHT values (" + overideMaxID + "," + ID2 + ")";
                        //InsertReToUr = "insert into MAP_RESERVATION_USER values (" + maxID + "," + "'eget@sem.ca')";
                        //rset = stmt.executeQuery(InsertReservation);
                        rset = stmt.executeQuery(InsertReToFl);
                        //rset = stmt.executeQuery(InsertReToUr);

                        if (price3 != 0) {
                            //InsertReservation = "insert into RESERVATION values (" + maxID + ",'SHIT', 'SHIT', '134'," + price3 + "," + "NULL)";
                            InsertReToFl = "insert into MAP_RESERVATION_FLIGHT values (" + overideMaxID + "," + ID3 + ")";
                            //InsertReToUr = "insert into MAP_RESERVATION_USER values (" + maxID + "," + "'eget@sem.ca')";
                            //rset = stmt.executeQuery(InsertReservation);
                            rset = stmt.executeQuery(InsertReToFl);
                            //rset = stmt.executeQuery(InsertReToUr);

                            if (price4 != 0) {
                                //InsertReservation = "insert into RESERVATION values (" + overideMaxID + ",'SHIT', 'SHIT', '134'," + price4 + "," + "NULL)";
                                InsertReToFl = "insert into MAP_RESERVATION_FLIGHT values (" + overideMaxID + "," + ID4 + ")";
                                //InsertReToUr = "insert into MAP_RESERVATION_USER values (" + maxID + "," + "'eget@sem.ca')";
                                //rset = stmt.executeQuery(InsertReservation);
                                rset = stmt.executeQuery(InsertReToFl);
                                //rset = stmt.executeQuery(InsertReToUr);

                                if (price5 != 0) {
                                    //InsertReservation = "insert into RESERVATION values (" + maxID + ",'SHIT', 'SHIT', '134'," + price5 + "," + "NULL)";
                                    InsertReToFl = "insert into MAP_RESERVATION_FLIGHT values (" + overideMaxID + "," + ID5 + ")";
                                    //InsertReToUr = "insert into MAP_RESERVATION_USER values (" + maxID + "," + "'eget@sem.ca')";
                                    //rset = stmt.executeQuery(InsertReservation);
                                    rset = stmt.executeQuery(InsertReToFl);
                                    //rset = stmt.executeQuery(InsertReToUr);

                                }
                            }
                        }
                    }
                }
            }

            //in general, this should be assigned (first run a query), save them to the fields and passed by pageCtl everytime when switching to a new page
            pageBean.UserID = UID;

            String UserSelect = "select * from USERS where USERS_EMAIL = '" + pageBean.UserID +"'";
            rset = stmt.executeQuery(UserSelect);
            rset.next();

            pageBean.CardType = rset.getString("USERS_CREDIT_CARD_TYPE");

            pageBean.CardNumber = rset.getString("USERS_CREDIT_CARD_NUMBER");

            //for temporary use, each address attribute should be initialized when the user login
            String addr = rset.getString("USERS_BILLING_ADDRESS");
            if ( addr != null ){
                StringTokenizer st = null;
                st = new StringTokenizer(addr,"-");
                pageBean.UserAddr = st.nextToken();
                pageBean.City = st.nextToken();
                pageBean.State = st.nextToken();
                pageBean.ZipCode = st.nextToken();
            }
            
            rset.close();
            }
               
            stmt.close();
            con.close();

            pageBean.data = v;

            return pageBean;
        }
        catch(Exception e) {
            e.printStackTrace();
            throw e;
        }
    }
}
